﻿using Ionic.Zip;
using SQLServer.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;

namespace Client.Library
{
    public static class GetData
    {
        #region Excel Methods

        const string ExcelSalesCommand = @"SELECT * FROM [Sales$]";
        const string ExcelWildcard = "*.xls";

        public static List<Sale> GetSalesFromZipFile(string location)
        {
            string unpackDirectory = Path.GetDirectoryName(location);

            using (ZipFile zip = ZipFile.Read(location))
            {
                foreach (var entry in zip)
                {
                    entry.Extract(unpackDirectory, ExtractExistingFileAction.OverwriteSilently);
                }
            }

            List<Sale> sales = GetSalesFromFolder(unpackDirectory + '\\' + 
                Path.GetFileNameWithoutExtension(location));
            return sales;
        }

        public static List<Sale> GetSalesFromFolder(string location)
        {
            List<Sale> sales = new List<Sale>();

            foreach (var file in Directory.EnumerateFiles(location, ExcelWildcard))
            {
                sales.AddRange(GetSalesFromExcelFile(file));
            }

            foreach (var subdir in Directory.EnumerateDirectories(location))
            {
                GetSalesFromFolder(subdir);
            }
            
            return sales;
        }

        public static List<Sale> GetSalesFromExcelFile(string location)
        {
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = location;
            csbuilder.Add("Extended Properties", "Excel 12.0; HDR=YES");


            DataTable table = new DataTable("Sales");

            using (OleDbConnection connectionXLS = new OleDbConnection(csbuilder.ConnectionString))
            {
                connectionXLS.Open();
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(ExcelSalesCommand, connectionXLS))
                {
                    adapter.FillSchema(table, SchemaType.Source);

                    adapter.Fill(table);
                }
            }

            List<Sale> sales = new List<Sale>();
            SupermarketEntities connectionDB = new SupermarketEntities();
            using (connectionDB)
            {
                int supermarketId = 0;
                int isHeader = 0;
                foreach (DataRow row in table.Rows)
                {
                    if (isHeader < 2)
                    {
                        if (isHeader == 0)
                        {
                            supermarketId = GetSupermarketId((string)row[0], connectionDB);
                        }
                        isHeader++;
                        continue;
                    }

                    Sale currentSale = new Sale()
                    {
                        SupermarketID = supermarketId
                    };

                    if ((string)row.ItemArray[0] == "…")
                    {
                        break;
                    }

                    currentSale.ProductID = Convert.ToInt32(row.ItemArray[0]);

                    currentSale.Quantity = Convert.ToInt32(row.ItemArray[1]);

                    currentSale.OrderDate = GetSaleDateFromLocation(location);

                    sales.Add(currentSale);
                }
            }

            return sales;
        }

        private static int GetSupermarketId(string supermarketName, SupermarketEntities connection)
        {
            AddSupermarketIfDoesntExist(supermarketName, connection);

            var supermarket = (from sm in connection.Supermarkets
                               where sm.Location == supermarketName
                               select sm.ID).First();

            return supermarket;
        }

        public static void AddSupermarketIfDoesntExist(string supermarketName, SupermarketEntities connection)
        {
            bool hasSupermarket = connection.Supermarkets.Any(sn => sn.Location == supermarketName);

            if (!hasSupermarket)
            {
                Supermarket newSupermarket = new Supermarket()
                {
                    Location = supermarketName
                };

                connection.Supermarkets.Add(newSupermarket);
                connection.SaveChanges();
            }
        }

        private static string GetConnectionStringExcel(string location)
        {
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + location + 
                                        ";Extended Properties=Excel 8.0;";

            return connectionString;
        }

        private static DateTime GetSaleDateFromLocation(string fileLocation)
        {
            string fileName = Path.GetFileNameWithoutExtension(fileLocation);

            string dateAsString = fileName.Substring(fileName.Length - 11);

            return DateTime.Parse(dateAsString);
        }

        #endregion
    }
}
